/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package pe.core.db;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;
import pe.core.ws.beans.ActualizaResult;
import pe.core.ws.beans.AnalistaResult;
import pe.core.ws.beans.ConsecionarioResult;
import pe.core.ws.beans.CronogramaResult;
import pe.core.ws.beans.EnviaAreaCentralResult;
import pe.core.ws.beans.ModeloResult;
import pe.core.ws.beans.UbigeoResult;

/**
 *
 * @author romulogalindo
 */
public class DBConnect {

    public DBConnect() {
    }

    public long getSesion(String query) {
        Context ctx = null;
        DataSource factory = null;
        AnalistaResult analista = null;
        Connection con = null;
        long id_sesion = 0;
        try {
            ctx = new InitialContext();
            factory = (DataSource) ctx.lookup("java:comp/env/poolPgWfacr");
            con = factory.getConnection();
            System.out.println("[Standander ws][getSesion] [Query:" + query + "]");
            ResultSet rpta = con.createStatement().executeQuery(query);
            while (rpta.next()) {
                id_sesion = rpta.getInt(1);
            }
            con.close();
            return id_sesion;
        } catch (SQLException | NamingException ep) {
            try {
                if (con != null && con.isClosed() == false) {
                    con.close();
                }
            } catch (SQLException ep2) {
            }
            System.out.println("[Standander ws][getSesion] Error:" + ep.getMessage());
            ep.printStackTrace();
            return 0;
        } finally {
            con = null;
        }
    }

    public AnalistaResult getEvaluacionAnalista(String query) {
        Context ctx = null;
        DataSource factory = null;
        AnalistaResult analista = null;
        Connection con = null;
        try {
            ctx = new InitialContext();
            factory = (DataSource) ctx.lookup("java:comp/env/poolPgWfacr");

            con = factory.getConnection();
            System.out.println("[Standander ws][getEvaluacionAnalista] [Query:" + query + "]");
            ResultSet rpta = con.createStatement().executeQuery(query);
            analista = new AnalistaResult();
            while (rpta.next()) {
                analista.setCo_expedi(rpta.getInt(1));
                analista.setTi_estado(rpta.getString(2));
                analista.setDe_estado(rpta.getString(3));
            }
            con.close();
            return analista;
        } catch (SQLException | NamingException ep) {
            try {
                if (con != null && con.isClosed() == false) {
                    con.close();
                }
            } catch (SQLException ep2) {
            }
            System.out.println("[Standander ws][getEvaluacionAnalista] Error:" + ep.getMessage());
            ep.printStackTrace();
            return null;
        } finally {
            con = null;
        }

    }

    public List<ModeloResult> getModelos(String query) {
        Context ctx = null;
        DataSource factory = null;
        List<ModeloResult> modelos = null;
        Connection con = null;
        try {
            ctx = new InitialContext();
            factory = (DataSource) ctx.lookup("java:comp/env/poolPgWfacr");
            modelos = new ArrayList<>();
            con = factory.getConnection();
            System.out.println("[Standander ws][getModelos] [Query:" + query + "]");
            ResultSet rpta = con.createStatement().executeQuery(query);
            while (rpta.next()) {
                ModeloResult modelo = new ModeloResult();
                modelo.setCo_modelo(rpta.getInt(1));
                modelo.setNo_modelo(rpta.getString(2));
                modelos.add(modelo);
            }
            con.close();
            return modelos;
        } catch (SQLException | NamingException ep) {
            try {
                if (con != null && con.isClosed() == false) {
                    con.close();
                }
            } catch (SQLException ep2) {
            }
            System.out.println("[Standander ws][getModelos] Error:" + ep.getMessage());
            ep.printStackTrace();
            return null;
        } finally {
            con = null;
        }
    }

    public ActualizaResult getActualizar(String query) {
        Context ctx = null;
        DataSource factory = null;
        ActualizaResult actualiza = null;
        Connection con = null;
        try {
            ctx = new InitialContext();
            factory = (DataSource) ctx.lookup("java:comp/env/poolPgWfacr");
            con = factory.getConnection();
            System.out.println("[Standander ws][getActualizar] [Query:" + query + "]");
            ResultSet rpta = con.createStatement().executeQuery(query);
            actualiza = new ActualizaResult();
            while (rpta.next()) {
                actualiza.setCo_estado(rpta.getInt(1));
                actualiza.setNo_estado(rpta.getString(2));
            }
            con.close();
            return actualiza;
        } catch (SQLException | NamingException ep) {
            try {
                if (con != null && con.isClosed() == false) {
                    con.close();
                }
            } catch (SQLException ep2) {
            }
            System.out.println("[Standander ws][getActualizar] Error:" + ep.getMessage());
            ep.printStackTrace();
            return null;
        } finally {
            con = null;
        }
    }

    public List<ConsecionarioResult> getConsecionarios(String query) {
        Context ctx = null;
        DataSource factory = null;
        List<ConsecionarioResult> consecionarios = null;
        Connection con = null;
        try {
            ctx = new InitialContext();
            factory = (DataSource) ctx.lookup("java:comp/env/poolPgWfacr");
            consecionarios = new ArrayList<>();
            con = factory.getConnection();
            System.out.println("[Standander ws][getConsecionarios] [Query:" + query + "]");
            ResultSet rpta = con.createStatement().executeQuery(query);
            while (rpta.next()) {
                ConsecionarioResult modelo = new ConsecionarioResult();
                modelo.setCo_conses(rpta.getInt(1));
                modelo.setNo_conces(rpta.getString(2));
                consecionarios.add(modelo);
            }
            con.close();
            return consecionarios;
        } catch (SQLException | NamingException ep) {
            try {
                if (con != null && con.isClosed() == false) {
                    con.close();
                }
            } catch (SQLException ep2) {
            }
            System.out.println("[Standander ws][getConsecionarios] Error:" + ep.getMessage());
            ep.printStackTrace();
            return null;
        } finally {
            con = null;
        }
    }

    public List<CronogramaResult> getCronograma(String query) {
        Context ctx = null;
        DataSource factory = null;
        List<CronogramaResult> cronogramas = null;
        Connection con = null;
        try {
            ctx = new InitialContext();
            factory = (DataSource) ctx.lookup("java:comp/env/poolPgWfacr");
            cronogramas = new ArrayList<>();
            con = factory.getConnection();
            System.out.println("[Standander ws][getCronograma] [Query:" + query + "]");
            ResultSet rpta = con.createStatement().executeQuery(query);
            SimpleDateFormat f = new SimpleDateFormat("yyyy-MM-dd");
            while (rpta.next()) {
                CronogramaResult cronograma = new CronogramaResult();
                cronograma.setCo_expedi(rpta.getLong(1));
                cronograma.setNu_cuotas(rpta.getInt(2));
                cronograma.setFe_vencim(f.format(rpta.getDate(3)));//---
                cronograma.setIm_salini(rpta.getDouble(4));
                cronograma.setIm_cuotas(rpta.getDouble(5));
                cronograma.setIm_capita(rpta.getDouble(6));
                cronograma.setIm_intere(rpta.getDouble(7));
                cronograma.setIm_salfin(rpta.getDouble(8));
                cronograma.setFe_inicio(f.format(rpta.getDate(9)));//----
                cronograma.setCa_diacuo(rpta.getInt(10));
                cronograma.setIm_seguro(rpta.getDouble(11));
                cronograma.setNo_sbsrci(rpta.getString(12));
                cronogramas.add(cronograma);
            }
            con.close();
            return cronogramas;
        } catch (SQLException | NamingException ep) {
            try {
                if (con != null && con.isClosed() == false) {
                    con.close();
                }
            } catch (SQLException ep2) {
            }
            System.out.println("[Standander ws][getCronograma] Error:" + ep.getMessage());
            ep.printStackTrace();
            return null;
        } finally {
            con = null;
        }
    }

    public EnviaAreaCentralResult getEnviaAreaCentral(String query) {
        Context ctx = null;
        DataSource factory = null;
        EnviaAreaCentralResult areaCentral = null;
        Connection con = null;
        try {
            ctx = new InitialContext();
            factory = (DataSource) ctx.lookup("java:comp/env/poolPgWfacr");
            con = factory.getConnection();
            System.out.println("[Standander ws][getEnviaAreaCentral] [Query:" + query + "]");
            ResultSet rpta = con.createStatement().executeQuery(query);
            areaCentral = new EnviaAreaCentralResult();
            while (rpta.next()) {
                areaCentral.setF_an_eacsanftp(rpta.getString(1));
            }
            con.close();
            return areaCentral;
        } catch (SQLException | NamingException ep) {
            try {
                if (con != null && con.isClosed() == false) {
                    con.close();
                }
            } catch (SQLException ep2) {
            }
            System.out.println("[Standander ws][getEnviaAreaCentral] Error:" + ep.getMessage());
            ep.printStackTrace();
            return null;
        } finally {
            con = null;
        }
    }

    public List<UbigeoResult> getUbigeosLima(String query) {
        Context ctx = null;
        DataSource factory = null;
        List<UbigeoResult> ubigeos = null;
        Connection con = null;
        try {
            ctx = new InitialContext();
            factory = (DataSource) ctx.lookup("java:comp/env/poolPgWfacr");
            ubigeos = new ArrayList<>();
            con = factory.getConnection();
            System.out.println("[Standander ws][getUbigeosLima] [Query:" + query + "]");
            ResultSet rpta = con.createStatement().executeQuery(query);
            while (rpta.next()) {
                UbigeoResult ubigeo = new UbigeoResult();
                ubigeo.setCo_ubigeo(rpta.getInt(1));
                ubigeo.setNo_distri(rpta.getString(2));
                ubigeos.add(ubigeo);
            }
            con.close();
            return ubigeos;
        } catch (SQLException | NamingException ep) {
            try {
                if (con != null && con.isClosed() == false) {
                    con.close();
                }
            } catch (SQLException ep2) {
            }
            System.out.println("[Standander ws][getCronograma] Error:" + ep.getMessage());
            ep.printStackTrace();
            return null;
        } finally {
            con = null;
        }
    }

}
